SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- DROP PROCEDURE pa_corrige_datos_duplicado
CREATE PROCEDURE [dbo].[pa_corrige_datos_duplicado]
AS
declare @agente int
declare @capacitacion int
declare @cant int
declare @idcapacitacion int
BEGIN
    SET NOCOUNT ON;
	select agente,capacitacion, count(*) as cant 
				into #tmp_cap
				from tb_capacitacionesdelagente
					group by agente,capacitacion
					having count(*)>1
					order by agente;
    DECLARE C1 CURSOR FOR select * from #tmp_cap;
 
    OPEN C1
    FETCH NEXT FROM C1 INTO @agente,@capacitacion,@cant
    WHILE @@FETCH_STATUS = 0
        BEGIN
			IF @cant = 2
			BEGIN
				--SET @idcapacitacion = (SELECT top 1 idcapacitacion FROM tb_capacitacionesdelagente WHERE agente=@agente and capacitacion=@capacitacion);
				DELETE FROM tb_capacitacionesdelagente WHERE idcapacitacion IN (SELECT top 1 idcapacitacion FROM tb_capacitacionesdelagente WHERE agente=@agente and capacitacion=@capacitacion);
			END
			IF @cant = 3
			BEGIN
				--SET @idcapacitacion = (SELECT top 1 idcapacitacion FROM tb_capacitacionesdelagente WHERE agente=@agente and capacitacion=@capacitacion);
				DELETE FROM tb_capacitacionesdelagente WHERE idcapacitacion IN (SELECT top 2 idcapacitacion FROM tb_capacitacionesdelagente WHERE agente=@agente and capacitacion=@capacitacion);
			END
			
            FETCH NEXT FROM C1 INTO @agente,@capacitacion,@cant
        END
  close C1
  deallocate C1
END
GO
